﻿Imports System.Data.SqlClient

Public Class FrmNhapHoSoBienDong
    Sub NapLoaiDat1()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select kyhieu, tenld from LOAIDAT", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbLD.DisplayMember = "kyhieu"
        CmbLD.ValueMember = "kyhieu"
        CmbLD.DataSource = tbl
        ob_cnn.Close()
    End Sub

    Private Sub NapDonViHuyen()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TENHUYEN", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbHuyen.DisplayMember = "T_huyen"
        CmbHuyen.ValueMember = "maH"
        CmbHuyen.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapLoaiTaiLieu()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMTaiLieu", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbLoaiTaiLieu.DisplayMember = "TenLoaiTaiLieu"
        CmbLoaiTaiLieu.ValueMember = "maLoaiTaiLieuID"
        CmbLoaiTaiLieu.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapPhong()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMPhong", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        cmbPhong.DisplayMember = "Tenphong"
        cmbPhong.ValueMember = "maPhongID"
        cmbPhong.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapNgan()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMNgan", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbNgan.DisplayMember = "TenNgan"
        CmbNgan.ValueMember = "maNganID"
        CmbNgan.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapKe()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMKe", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbKe.DisplayMember = "TenKe"
        CmbKe.ValueMember = "maKeID"
        CmbKe.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapKho()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMKho", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbKho.DisplayMember = "TenKho"
        CmbKho.ValueMember = "maKhoID"
        CmbKho.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapTinhTrang()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMTinhTrang", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbTinhTrang.DisplayMember = "TenTinhTrang"
        CmbTinhTrang.ValueMember = "maTinhTrangID"
        CmbTinhTrang.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapLoaiBienDong()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from LOAIBD", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        cbLoaiBienDong.DisplayMember = "TenLoaiBD"
        cbLoaiBienDong.ValueMember = "maBD"
        cbLoaiBienDong.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Public Function checkID(ByVal ID As String) As Integer
        ob_cnn.Open()
        Dim sqlCom As New SqlCommand("select count(sohoso) from TENCHUBIENDONG where SOHOSO='" & ID & "' ", ob_cnn)
        Dim count As Int32 = Convert.ToInt16(sqlCom.ExecuteScalar())
        ob_cnn.Close()
        Return count
    End Function
    Public Function checkSoHoSo()
        ob_cnn.Open()
        Dim sqlCom As New SqlCommand("select max(sohoso) from TENCHUBIENDONG  ", ob_cnn)
        Dim max As Int32 = Convert.ToInt32(sqlCom.ExecuteScalar())
        ob_cnn.Close()
        Return max
    End Function
    Private Sub Them_Du_lieu()
        Try
            Dim NgayNhap As String
            NgayNhap = String.Format("{0:MM/dd/yyyy}", DateNgayNhap.Value)
            Dim Sql As String = ""
            Sql = " insert into TENCHUBIENDONG(SoHoSo,TenChu, DiaChi, XaID, HuyenID, TinhTrangID, PhongID, NganID, KeID, KhoID,  MaBienDong, LoaiBienDongID, NgayNhap ) " &
                " values (@SoHoSo, @TenChu, @DiaChi, @XaID, @HuyenID, @TinhTrangID, @PhongID, @NganID, @KeID, @KhoID, @MaBienDong, @LoaiBienDongID, @NgayNhap)"
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            cmd.Parameters.AddWithValue("@SoHoSo", TextBoxSoHoSo.Text)
            cmd.Parameters.AddWithValue("@TenChu", TextTenChu.Text)
            cmd.Parameters.AddWithValue("@DiaChi", TextDiaChi.Text)
            cmd.Parameters.AddWithValue("@XaID", CmbXa.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@HuyenID", CmbHuyen.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@TinhTrangID", CmbTinhTrang.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@PhongID", cmbPhong.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@NganID", CmbNgan.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@KeID", CmbKe.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@KhoID", CmbKho.SelectedValue.ToString)
            cmd.Parameters.AddWithValue("@MaBienDong", TextMaBienDong.Text)
            cmd.Parameters.AddWithValue("@LoaiBienDongID", cbLoaiBienDong.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@NgayNhap", DateTime.Parse(NgayNhap))
            ob_cnn.Open()
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            ob_cnn.Close()
            QuanlyNhap()
            MsgBox("Thêm thông tin thành công!")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub Them_thua_dat()
        Try
            Dim NgayNhap As String
            NgayNhap = String.Format("{0:MM/dd/yyyy}", DateNgayNhap.Value)
            Dim Sql As String = ""
            Sql = " insert into THUADATBIENDONG(SoHoSo, ThuaDat, ToBanDo, DienTich,LoaiDat,LoaiTaiLieu, " &
                " XaID, HuyenID, ngaynhap ) values (@SoHoSo, @ThuaDat, @ToBanDo, @DienTich, @LoaiDat, @LoaiTaiLieu, @XaID, @HuyenID, @NgayNhap)"
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            cmd.Parameters.AddWithValue("@SoHoSo", TextBoxSoHoSo.Text)
            cmd.Parameters.AddWithValue("@ThuaDat", TextSoThua.Text)
            cmd.Parameters.AddWithValue("@ToBanDo", TextToBD.Text)
            cmd.Parameters.AddWithValue("@DienTich", TextDienTich.Text)
            cmd.Parameters.AddWithValue("@LoaiDat", CmbLD.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@LoaiTaiLieu", CmbLoaiTaiLieu.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@XaID", CmbXa.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@HuyenID", CmbHuyen.SelectedValue.ToString())
            cmd.Parameters.AddWithValue("@NgayNhap", DateTime.Parse(NgayNhap))
            ob_cnn.Open()
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub LamTuoi()
        TextTenChu.Text = ""
        TextDiaChi.Text = ""
        TextBoxSoHoSo.Text = ""
        TextSoThua.Text = ""
        TextToBD.Text = ""
        TextDienTich.Text = ""
        TextMaBienDong.Text = ""

    End Sub
    Public Sub NapDuLieuNhapVaodataGird()
        DataGridViewThuaDat.Rows.Add(TextBoxSoHoSo.Text, TextSoThua.Text, TextToBD.Text, TextDienTich.Text, CmbLD.SelectedValue.ToString)
    End Sub
    Public Sub xoadatagrid()
        DataGridViewThuaDat.Rows.Clear()
    End Sub
    Private Sub QuanlyNhap()
        Try
          
            Dim tennhap As String
            tennhap = ModuleTenDangNhap.m_tennhap
            Dim Sql As String = ""
            Sql = " insert into QL_NhapBienDong(SoHoSoBienDong,NguoiNhap, NgayNhap) values (@SoHoSoBienDong, @NguoiNhap, @NgayNhap)"

            Dim cmd As New SqlCommand(Sql, ob_cnn)
            cmd.Parameters.AddWithValue("@SoHoSoBienDong", TextBoxSoHoSo.Text)
            cmd.Parameters.AddWithValue("@NguoiNhap", tennhap)
            cmd.Parameters.AddWithValue("@NgayNhap", DateTime.Now)
            ob_cnn.Open()
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub FrmNhapHoSoBienDong_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        NapLoaiTaiLieu()
        NapDonViHuyen()
        NapLoaiDat1()
        NapLoaiBienDong()
        NapPhong()
        NapNgan()
        NapKe()
        NapKho()
        NapTinhTrang()
        TextBoxSoHoSo.Text = checkSoHoSo() + 1
        Me.ButtonLuu.Enabled = False
        Me.ButtonMucLuc.Enabled = False
        DataGridViewThuaDat.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewThuaDat.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
        DataGridViewTenchuBiendong.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewTenchuBiendong.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
    End Sub

    Private Sub ButtonLuu_Click(sender As Object, e As EventArgs) Handles ButtonLuu.Click
        If (checkID(TextBoxSoHoSo.Text) > 0) Then
            MsgBox(" Số hồ sơ không được trùng!")
            Exit Sub
        End If
        If TextBoxSoHoSo.Text = "" Then
            MessageBox.Show("Chi số biến động không được trống ")
            Return
        Else
            Them_Du_lieu()
            LamTuoi()
            xoadatagrid()
            TextBoxSoHoSo.Text = checkSoHoSo() + 1
            ob_cnn.Close()

        End If
    End Sub

    Private Sub ButtonNhap_Click(sender As Object, e As EventArgs) Handles ButtonNhap.Click
        If TextTenChu.Text = "" Then
            MessageBox.Show("Tên chủ sử dụng không được rỗng")
            Return
        End If
        If TextDiaChi.Text = "" Then
            MessageBox.Show("Chưa nhập địa chỉ chủ sử dụng đất")
            Return
        End If
        If TextBoxSoHoSo.Text = "" Then
            MessageBox.Show("Số hồ sơ không được rỗng")
            Return
        End If
        If (checkID(TextBoxSoHoSo.Text) > 0) Then
            MsgBox(" Đã trùng số hồ sơ, bạn kiểm tra lại ")
            'Exit Sub
            Return
        End If

        If TextToBD.Text = "0" Or TextToBD.Text = "" Then
            MessageBox.Show("Chưa nhập thông tin tờ bản đồ")
            Return
        End If
        If TextToBD.Text < "0" Then
            MessageBox.Show("Tờ bản đồ không được nhỏ hơn 0")
            Return
        End If
        If TextSoThua.Text = "0" Or TextSoThua.Text = "" Then
            MessageBox.Show("Chưa nhập thông tin thửa đất")
            Return
        End If
        If TextSoThua.Text < "0" Then
            MessageBox.Show("Thửa đất phải lớn hơn 0 ")
            Return
        End If
        If TextDienTich.Text <= "0" Then
            MessageBox.Show(" Diện tích không được rỗng hoặc nhỏ hơn 0 ")
            Return
        Else
            Them_thua_dat()
            NapDuLieuNhapVaodataGird()
            Me.ButtonLuu.Enabled = True
            Me.ButtonMucLuc.Enabled = True
            ob_cnn.Close()
        End If
    End Sub

    Private Sub ButtonLoaiDat1_Click(sender As Object, e As EventArgs)
        Dim frm As New FrmLDat
        FrmLDat.Show()
    End Sub

    Private Sub CmbHuyen_SelectedIndexChanged(sender As Object, e As EventArgs) Handles CmbHuyen.SelectedIndexChanged
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TenXa where mahuyen = '" & CmbHuyen.SelectedValue.ToString & "' ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbXa.DisplayMember = "TenXa"
        CmbXa.ValueMember = "maxa"
        CmbXa.DataSource = tbl
        ob_cnn.Close()
    End Sub

    Private Sub ButtonThoat_Click(sender As Object, e As EventArgs) Handles ButtonThoat.Click
        Me.Close()
    End Sub

    
    Private Sub DataGridViewThuaDat_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewThuaDat.CellContentClick

    End Sub

    Private Sub DataGridViewThuaDat_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewThuaDat.RowPostPaint
        ' xuat so thu tu tu dong cho datagridview
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewThuaDat.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewThuaDat.RowHeadersWidth < Size.Width + 20 Then DataGridViewThuaDat.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))
    End Sub
    Private Sub NapDataGridViewtenchu()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select TenChu, DiaChi " &
                                " From TENCHUBIENDONG , tenxa x, tenhuyen h" &
                                " where xaid = x.maxa " &
                                " and huyenid = h.mah " &
                                " and xaid = '" & CmbXa.SelectedValue.ToString & "' " &
                                " and huyenid = '" & CmbHuyen.SelectedValue.ToString & "' " &
                                " and TenChu like N'%" & TextTenChu.Text & "%' group by tenchu, diachi", ob_cnn) '
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewTenchuBiendong.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapVaoTexBox()
        TextTenChu.DataBindings.Clear()
        TextTenChu.DataBindings.Add("Text", DataGridViewTenchuBiendong.DataSource, "tenchu")
        TextDiaChi.DataBindings.Clear()
        TextDiaChi.DataBindings.Add("Text", DataGridViewTenchuBiendong.DataSource, "diachi")
    End Sub
    Private Sub TextTenChu_TextChanged(sender As Object, e As EventArgs) Handles TextTenChu.TextChanged
        NapDataGridViewtenchu()
    End Sub
    Private Sub DataGridViewTenchuBiendong_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewTenchuBiendong.CellClick
        '  NapVaoTexBox()
    End Sub

    Private Sub DataGridViewTenchuBiendong_CellDoubleClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewTenchuBiendong.CellDoubleClick
        NapVaoTexBox()
    End Sub

    Private Sub DataGridViewTenchuBiendong_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewTenchuBiendong.RowPostPaint
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewTenchuBiendong.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewTenchuBiendong.RowHeadersWidth < Size.Width + 20 Then DataGridViewTenchuBiendong.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))
    End Sub

    Private Sub ButtonMucLuc_Click(sender As Object, e As EventArgs) Handles ButtonMucLuc.Click
        Dim f As FrmMucLucHoSoBienDong = New FrmMucLucHoSoBienDong()
        f.Show()
    End Sub
End Class